﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Model;
using DataAccess.SqlClient;
using System.Data;
using System.Data.SqlClient;
namespace DAL
{
 public   class CounselingDAL
    {
        /// <summary>
        ///健康咨询表分页显示
        /// </summary>
        /// <param name="pageindex"></param>
        /// <param name="pagesize"></param>
        /// <returns></returns>
     public static dynamic Counselingshow(int pageindex, int pagesize)
        {
            var q = new SqlQuery
            {
                CommandText = "select * from (select ROW_NUMBER() over(order by InTime) nid, * from Counseling where CounselingTID=@CounselingTID) a where a.nid between (@pageindex-1)*@pagesize+1 and (@pageindex*@pagesize)",
            };
            q.Parameters.Add("@pageindex", pageindex, SqlDbType.Int);//给参数赋值
            q.Parameters.Add("@pagesize", pagesize, SqlDbType.Int);
            var dt = SqlDataAccess.ExecDataset(q).Tables[0];//连接数据库，获取健康咨询类型表
            var list = new List<dynamic>();
            foreach (var dr in dt.Select())
            {
                list.Add(

                    new
                    {
                        CounselingTID = dr.Field<Guid>("CounselingTID"),
                        CounselingID = dr.Field<Guid>("CounselingID"),
                        Title = dr.Field<string>("Title"),
                        InTime=dr.Field<DateTime>("InTime").ToString(),
                        CounImage = dr.Field<string>("CounImage"),
                        Counts = dr.Field<int>("Counts"),
                        Content = dr.Field<string>("Content"),
                        jianjie = dr.Field<string>("jianjie"),
                    }
                    );
            }
            return list;
        }
        /// <summary>
        /// 获取健康咨询表数量
        /// </summary>
        /// <returns></returns>
        public static int Counselingcount()
        {
            var q = new SqlQuery
            {
                CommandText = "select count(*) from [Counseling]",
            };
            return Convert.ToInt32(SqlDataAccess.ExecScalar(q));
        }
     /// <summary>
     /// 获取健康资讯类型表
     /// </summary>
     /// <returns></returns>

        public static dynamic CounselingTypeshow()
        {
            SqlQuery q = new SqlQuery
            {
                CommandText = "select * from CounselingType",
            };
            var dt = SqlDataAccess.ExecDataset(q).Tables[0];//连接数据库，获取健康资讯类型表
            var list = new List<dynamic>();
            foreach (var dr in dt.Select())
            {
                list.Add(

                    new
                    {
                        CounselingTID = dr.Field<Guid>("DoctorID"),
                        CounselingType = dr.Field<string>("CounselingType"),
                    }
                    );
            }
            return list;
        }

      /// <summary>
        ///热门咨询分页显示
        /// </summary>
        /// <param name="pageindex"></param>
        /// <param name="pagesize"></param>
        /// <returns></returns>
        public static dynamic remenzixunshow(int pageindex, int pagesize)
        {
            var q = new SqlQuery
            {
                CommandText = "select * from(select ROW_NUMBER() OVER(ORDER by Counts) nid,a.CounImage,a.CounselingID,a.Title,a.jianjie,a.CounselingTID from Counseling a ) b where b.nid between (@pageindex-1)*@pagesize+1 and (@pageindex*@pagesize)",
            };
            q.Parameters.Add("@pageindex", pageindex, SqlDbType.Int);//给参数赋值
            q.Parameters.Add("@pagesize", pagesize, SqlDbType.Int);
            var dt = SqlDataAccess.ExecDataset(q).Tables[0];//连接数据库，获取热门咨询分页显示
            var list = new List<dynamic>();
            foreach (var dr in dt.Select())
            {
                list.Add(

                    new
                    {
                        CounselingTID = dr.Field<Guid>("CounselingTID"),
                        CounselingID = dr.Field<Guid>("CounselingID"),
                        Title = dr.Field<string>("Title"),
                        CounImage = dr.Field<string>("CounImage"),
                        jianjie = dr.Field<string>("jianjie"),
                    }
                    );
            }
            return list;
        }
        /// <summary>
        /// 热门咨询数量
        /// </summary>
        /// <returns></returns>
        public static int zixuncountcount()
        {
            var q = new SqlQuery
            {
                CommandText = "select count(*) from [Counseling]",
            };
            return Convert.ToInt32(SqlDataAccess.ExecScalar(q));
        }
    }
}
